package com.denlaku.longan.sql;

import com.denlaku.longan.SqlConst;
import org.apache.commons.collections4.CollectionUtils;
import org.apache.commons.collections4.ListUtils;
import org.apache.commons.lang3.StringUtils;
import org.springframework.stereotype.Component;

import java.util.ArrayList;
import java.util.List;

/**
 * @author tianx
 */
@Component
public class SqlCommandBuilder {

	public SqlCommand build(SqlInfo info) {
		StringBuilder sb = new StringBuilder();

		sb.append(SqlConst.SELECT_);
		processSelectItems(info, sb);
		sb.append(SqlConst.FROM_);
		sb.append(info.getTableName());

		List<Object> params = new ArrayList<>();
		processWhereItems(info, sb, params);
		processGroupByItems(info, sb, params);

		processOrderByItems(info, sb);

		Limit limit = info.getLimit();
		if (limit != null) {
			sb.append(SqlConst.NEXT_LINE);
			buildLimit(limit, sb);
		}

		SqlCommand sqlCommand = new SqlCommand();
		sqlCommand.setSql(sb.toString());
		sqlCommand.setParams(params);
		return sqlCommand;
	}

	private void processGroupByItems(SqlInfo info, StringBuilder sb, List<Object> params) {
		List<GroupByItem> groupByItems = info.getGroupByItems();
		List<HavingItem> havingItems = info.getHavingItems();
		boolean hasGroupBy = CollectionUtils.isNotEmpty(groupByItems);
		boolean hasHaving = CollectionUtils.isNotEmpty(havingItems);
		if (!hasGroupBy && hasHaving) {
			sb.append(SqlConst.NEXT_LINE);
			sb.append(SqlConst.GROUP_BY_NULL_);
		}
		if (hasGroupBy) {
			sb.append(SqlConst.NEXT_LINE);
			sb.append(SqlConst.GROUP_BY_);
			for (int i = 0; i < groupByItems.size(); i++) {
				if (i != 0) {
					sb.append(SqlConst.COMMA);
				}
				buildGroupBy(groupByItems.get(i), sb);
			}
		}

		if (hasGroupBy & hasHaving) {
			sb.append(SqlConst.NEXT_LINE);
			sb.append(SqlConst.HAVING_);
			for (int i = 0; i < havingItems.size(); i++) {
				if (i != 0) {
					sb.append(SqlConst.NEXT_LINE);
					sb.append(SqlConst.AND_);
				}
				buildHaving(havingItems.get(i), sb, params);
			}
		}
	}

	private void processOrderByItems(SqlInfo info, StringBuilder sb) {
		List<OrderByItem> orderByItems = info.getOrderByItems();
		if (CollectionUtils.isNotEmpty(orderByItems)) {
			sb.append(SqlConst.NEXT_LINE);
			sb.append(SqlConst.ORDER_BY_);
			for (int i = 0; i < orderByItems.size(); i++) {
				if (i != 0) {
					sb.append(SqlConst.COMMA);
					sb.append(SqlConst.NEXT_LINE);
				}
				buildOrderBy(orderByItems.get(i), sb);
			}
		}
	}

	private void processWhereItems(SqlInfo info, StringBuilder sb, List<Object> params) {
		List<WhereItem> whereItems = ListUtils.emptyIfNull(info.getWhereItems());
		if (whereItems.isEmpty()) {
			return;
		}
		sb.append(SqlConst.NEXT_LINE);
		sb.append(SqlConst._WHERE_);
		for (int i = 0; i < whereItems.size(); i++) {
			if (i != 0) {
				sb.append(SqlConst.NEXT_LINE);
				sb.append(SqlConst.AND_);
			}
			buildWhere(whereItems.get(i), sb, params);
		}
	}

	private void processSelectItems(SqlInfo info, StringBuilder sb) {
		List<SelectItem> selectItems = ListUtils.emptyIfNull(info.getSelectItems());
		for (int i = 0; i < selectItems.size(); i++) {
			buildSelect(selectItems.get(i), sb);
			if (i != selectItems.size() - 1) {
				sb.append(SqlConst.COMMA_);
			}
			sb.append(SqlConst.NEXT_LINE);
		}
	}

	private void buildSelect(SelectItem item, StringBuilder sb) {
		String name = item.getName();
		String aggregator = item.getAggregator();
		if (StringUtils.isNotEmpty(aggregator)) {
			sb.append(aggregator);
			sb.append(SqlConst.BRACKET_LEFT);
			sb.append(name);
			sb.append(SqlConst.BRACKET_RIGHT);
		} else {
			sb.append(name);
		}
		sb.append(SqlConst._AS_);
		sb.append(StringUtils.defaultIfEmpty(item.getAlias(), name));
	}

	private void buildWhere(WhereItem item, StringBuilder sb, List<Object> params) {
		String name = item.getName();
		String operator = item.getOperator();
		List<Object> values = item.getValues();
		if (operator == null) {
			boolean ifNull = item.isIfNull();
			boolean ifNotNull = item.isIfNotNull();
			if (ifNull) {
				sb.append(SqlConst._IS_NULL_);
			} else if (ifNotNull) {
				sb.append(SqlConst._IS_NOT_NULL_);
			}
			return;
		}

		if (CollectionUtils.isEmpty(values)) {
			return;
		}

		int size = values.size();
		if (size == 1) {
			sb.append(name);
			sb.append(operator);
			sb.append(SqlConst.PLACEHOLDER);
			params.add(values.getFirst());
			return;
		}

		String logic = SqlConst._OR_;
		if (SqlConst.NOT_EQUAL.equals(operator) || SqlConst.NOT_EQUAL_2.equals(operator)) {
			logic = SqlConst._AND_;
		}
		sb.append(SqlConst._BRACKET_LEFT);
		for (int i = 0; i < size; i++) {
			if (i != 0) {
				sb.append(logic);
			}
			sb.append(name);
			sb.append(operator);
			sb.append(SqlConst.PLACEHOLDER);
			params.add(values.get(i));
		}
		sb.append(SqlConst.BRACKET_RIGHT_);
	}

	private void buildGroupBy(GroupByItem item, StringBuilder sb) {
		String name = item.getName();
		sb.append(name);
	}

	private void buildHaving(HavingItem item, StringBuilder sb, List<Object> params) {
		List<Object> values = item.getValues();
		if (values == null || values.isEmpty()) {
			return;
		}
		String name = item.getName();
		String aggregator = item.getAggregator();
		String operator = item.getOperator();
		String tName = name;
		if (StringUtils.isNotEmpty(aggregator)) {
			tName = String.format("%s(%s)", aggregator, name);
		}
		int size = values.size();
		if (size == 1) {
			sb.append(tName);
			sb.append(aggregator);
			sb.append(SqlConst.PLACEHOLDER);
			params.add(values.getFirst());
		} else {
			String logic = SqlConst._OR_;
			if (SqlConst.NOT_EQUAL.equals(operator) || SqlConst.NOT_EQUAL_2.equals(operator)) {
				logic = SqlConst._AND_;
			}
			sb.append(SqlConst._BRACKET_LEFT);
			for (int i = 0; i < size; i++) {
				Object value = values.get(i);
				if (i != 0) {
					sb.append(logic);
				}
				sb.append(name);
				sb.append(SqlConst.PLACEHOLDER);
				params.add(value);
			}
			sb.append(SqlConst.BRACKET_RIGHT_);
		}
	}

	private void buildOrderBy(OrderByItem item, StringBuilder sb) {
		String name = item.getName();
		String order = item.getOrder();
		if (name == null || name.isEmpty() || order == null || order.isEmpty()) {
			return;
		}
		boolean has = false;
		if (SqlConst.DESC.equals(order) || SqlConst.ASC.equals(order)) {
			sb.append(name);
			sb.append(SqlConst.SPACE);
			sb.append(order);
			has = true;
		} else {
			List<Object> values = item.getValues();
			if (values != null && !values.isEmpty()) {
				sb.append(SqlConst.CASE_);
				sb.append(name);
				int n = 0;
				for (Object value: values) {
					sb.append(SqlConst._WHEN_);
					if (value instanceof String) {
						sb.append(SqlConst.SINGLE_QUOTE);
						sb.append(value);
						sb.append(SqlConst.SINGLE_QUOTE);
					} else {
						sb.append(value);
					}
					sb.append(SqlConst._THEN_);
					sb.append(n++);
				}
				sb.append(" else 99999");
				sb.append(" end asc");
				has = true;
			}
		}
		if (has) {
			if (item.isNullFirst()) {
				sb.append(SqlConst._NULLS_FIRST_);
			}
		}
	}
	
	private void buildLimit(Limit limit, StringBuilder sb) {
		int start = limit.getStart();
		int size = limit.getSize();
		sb.append(String.format(SqlConst.LIMIT_PATTERN, start, size));
	}

}
